package teset;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import util.SqliteUtils;

public class SqliteTest {

	private static final Logger LOG = LoggerFactory.getLogger(SqliteTest.class);
	
	public static void main(String[] args) {
		//
		String tableName = "api_dict";
		Connection conn = null;
		try {
			Statement stmt = null;
			conn = SqliteUtils.getConnection();
			stmt = conn.createStatement();
			if(!SqliteUtils.verifyTableExist(tableName)){
				stmt.executeUpdate("create table api_dict (id integer primary key, key string, val string, type string)");
			}
			ResultSet rs = stmt.executeQuery("select count(1) from api_dict");
			if(rs.next()){
				if(rs.getInt(1) == 0){
					stmt.executeUpdate("insert into api_dict (id, key, val, type) values(null, 'jdbc_url', '111', 'Dev')");
					stmt.executeUpdate("insert into api_dict (id, key, val, type) values(null, 'jdbc_username', 'root', 'Dev')");
					stmt.executeUpdate("insert into api_dict (id, key, val, type) values(null, 'jdbc_pwd', '1qaz@WSX', 'Dev')");
				}
			}
			
			rs = stmt.executeQuery("select * from api_dict");
			while(rs.next()){
				int id = rs.getInt("id");
				String key = rs.getString("key");
				String val = rs.getString("val");
				String type = rs.getString("type");
				System.out.println(String.format("id: %s, key: %s, val: %s, type: %s", id, key, val, type));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			LOG.info(e.getMessage());
		} finally {
			SqliteUtils.closeConnection(conn);
		}
		
	}
	
	
	
	
	
	
	
	
	
	public void test001(){
		Connection conn = null;
		try {
			conn = SqliteUtils.getConnection();
			Statement statement = conn.createStatement();
			statement.setQueryTimeout(30);
			// api_dict
			ResultSet rs = statement.executeQuery("SELECT COUNT(1) FROM sqlite_master where type='table' and name='api_dict'");
			if(rs.next()){
				System.out.println("==rs==\n");
				Integer count = rs.getInt(1);
				System.out.println(String.format("count : %s", count));
			}else{
				System.out.println("no table name 'api_dict'");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			SqliteUtils.closeConnection(conn);
		}
	}
	
	public void test01(){

		Connection connection = null;
		try {
			// create a database connection
			connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.

			ResultSet rs = statement.executeQuery("select * from person");
			while (rs.next()) {
				// read the result set
				System.out.println("name = " + rs.getString("name"));
				System.out.println("id = " + rs.getInt("id"));
			}
		} catch (SQLException e) {
			// if the error message is "out of memory",
			// it probably means no database file is found
			System.err.println(e.getMessage());
		} finally {
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				// connection close failed.
				System.err.println(e);
			}
		}
	
	}
	
	@Test
	public void test02(){
		String tpl = "SELECT COUNT(1) FROM sqlite_master WHERE type='table' AND name='{1}'";
		String tableName = "api_dict";
		String str = tpl.replace("{1}", tableName);
		System.out.println(str);
	}
}
